package com.xinzhitong.www.mapper;

import com.xinzhitong.www.pojo.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 培训表的相关操作
 *
 * @author 魏骆淳 钟伟良 徐鑫
 */
@Repository
public interface TrainMapper {

    @Select("select introduction, class_notice from train where id = #{trainId}")
    Train selectTrainInformation(@Param("trainId") Integer trainId);

    @Select("select picture from train_has_picture where train_id = #{trainId}")
    List<TrainHasPicture> selectTrainPicture(@Param("trainId") Integer trainId);

    /*@Select("select province, city, area, address " +
            "from train_class_location " +
            "where train_id = #{trainId} and train_class_No = #{trainClassNo} and state=\"已发布\"")
    List<TrainClassLocation> selectTrainClassLocation(
            @Param("trainId") Integer trainId,
            @Param("trainClassNo") Integer trainClassNo);

    @Select("select startTime, endTime " +
            "from train_class_time_view " +
            "where trainId = #{trainId} and trainClassNo = #{trainClassNo} and state=\"已发布\"")
    List<Map<String, Object>> selectTrainClassTime(
            @Param("trainId") Integer trainId,
            @Param("trainClassNo") Integer trainClassNo);*/

    @Select("select province, city, area, address " +
            "from train_organ_class_view " +
            "where train_id = #{trainId} and train_class_No = #{trainClassNo} and state=\"已发布\"")
    List<Map<String, Object>> selectTrainClassLocation(
            @Param("trainId") Integer trainId,
            @Param("trainClassNo") Integer trainClassNo);

    @Select("select start_time, end_time, number_limit, price, contact " +
            "from train_organ_class_view " +
            "where train_id = #{trainId} and train_class_No = #{trainClassNo} and state=\"已发布\"" +
            "and start_time>#{datetime}")
    List<Map<String, Object>> selectTrainClassTime(
            @Param("trainId") Integer trainId,
            @Param("trainClassNo") Integer trainClassNo,
            @Param("datetime") String datetime
    );

    @Select("select text, be_checked " +
            "from train_class_request " +
            "where train_id = #{trainId} and No = #{trainClassNo}")
    List<TrainClassRequest> selectTrainClassRequest(
            @Param("trainId") Integer trainId,
            @Param("trainClassNo") Integer trainClassNo);

    @Insert("insert into user_click_train_class_time (username,train_class_time_id,datetime) values(#{username},#{trainClassID},#{datetime})")
    void insertUserClickTrainClassTime(
            @Param("username") String username,
            @Param("trainClassID") Integer trainClassID,
            @Param("datetime")String datetime
    );

    @Select("select text from train_class where train_id = #{trainId} and No = #{trainClassNo}")
    TrainClass selectTrainClassText(@Param("trainId") Integer trainId, @Param("trainClassNo") Integer trainClassNo);

    @Select("select id, name, count from train_outline_and_count_view where credentialId = #{professionId}")
    List<Map<String, Object>> selectTrainOutline(@Param("professionId") Integer professionId);

    @Select("select " +
            "username, train_class_time_id " +
            "from user_has_train_class_time " +
            "where username = #{username} and train_class_time_id = #{trainClassTimeId}")
    UserHasTrainClassTime selectTrainApplyInformation(
            @Param("username") String username,
            @Param("trainClassTimeId") Integer trainClassTimeId);

    @Select("select train_class_time_id from user_has_train_class_time where train_class_time_id = #{trainClassTimeId}")
    List<UserHasTrainClassTime> selectTrainClassTimeId(@Param("trainClassTimeId") Integer trainClassTimeId);

    @Insert("insert into user_has_train_class_time(username, train_class_time_id) " +
            "values (#{username}, #{trainClassTimeId})")
    void insertTrainApplyInformation(
            @Param("username") String username,
            @Param("trainClassTimeId") Integer trainClassTimeId);

    @Select("select name, No from train_class where train_id = #{trainId}")
    List<TrainClass> selectTrainClassInformation(@Param("trainId") Integer trainId);

    @Select("select trainClassNo " +
            "from train_class_study_view " +
            "where trainId = #{trainId} and username = #{username}")
    List<Map<String, Object>> selectTrainClassStudy(
            @Param("trainId") Integer trainId,
            @Param("username") String username);

    @Select("select * from train_class_apply_information_view where trainClassTimeId = #{trainClassTimeId}")
    Map<String, Object> selectTrainClassApplyInformation(@Param("trainClassTimeId") Integer trainClassTimeId);

    @Select("select address,start_time,name,price,numberLimit,enrollNumber," +
            "end_time,picture,id,train_organ_id " +
            "from train_general_all " +
            "where city=#{city} and time=#{time}and train_id=#{trainId} and train_class_No=#{trainClassNo}")
    List<Map<String, Object>> selectTrainGeneral(String time, String city, Integer trainId, Integer trainClassNo);

    @Select("select type,number from train_general_contact where id=#{id}")
    List<Map<String, Object>> selectTrainGeneralContact(Integer id);

    @Select("select person,type,number from train_organ_has_contact where train_organ_id=#{tranOrganId}")
    List<TrainContact> selectContact(Integer trainOrganId);


    @Select("select time from user_has_train  where username=#{username} and start_time>=#{time1}" +
            "and start_time<#{time2} ")
    List<String> selectTime(String username, Date time1, Date time2);

    @Select("select dayDiff from user_has_train_day where username=#{username}")
    Integer selectDay(String username);

    @Select("select datetime, trainClassName, trainId, trainClassNo, fee " +
            "from train_class_system_message_view " +
            "where username = #{username} and trainClassTimeId = #{trainClassTimeId}")
    Map<String, Object> selectTrainSystemMessage(
            @Param("username") String username,
            @Param("trainClassTimeId") Integer trainClassTimeId
    );

    @Select("select count(0) from train_class_time where train_organ_id=#{trainOrganId}")
    Integer countTrainClassTimeOfTrainOrgan(@Param("trainOrganId") Integer trainOrganId);

    @Select("SELECT count(0)\n" +
            "FROM\n" +
            "buy\n" +
            "INNER JOIN train_class_time ON buy.good_id = train_class_time.id\n" +
            "where train_organ_id=#{trainOrganId} and type='TRAINCLASSTIME'")
    Integer countBuyTrainClassTimeOfTrainOrgan(@Param("trainOrganId") Integer trainOrganId);

    @Update("Update train_class_time SET state='已下架' where id=#{id}")
    Integer updateTrainClass(Integer id);


    @Delete("Delete from train_class_time  where id=#{id} ")
    void deleteTrainClassTime(Integer id);

    @Select("select No,name from train_class where train_id=#{train_id}")
    List<Map<String, Object>> selectTrainClassNoAndName(Integer train_id);

    @Select("select trainId,trainName from profession_to_train where professionId=#{professionId}")
    List<Map<String, Object>> selectTrainNameAndId(Integer professionId);

    @Select("SELECT id,name,count,numberLimit, passTime, publishTime,state" +
            " FROM train_class_general where state LIKE #{state} and name LIKE #{name}and username=#{username}")
    List<Map<String, Object>> selectTrainClassGeneral(String name, String state, String username);

    @Select("SELECT date,realname,userId,contact,username FROM user_enroll_train_class_condition where" +
            " trainClassId=#{trainClassId}")
    List<Map<String, Object>> SelectUserEnrollTrainClassCondition(Integer trainClassId);

    @Insert("insert into train_class_time(id, train_id, train_class_No, start_time, end_time, number_limit, price, " +
            "state, contact, start_clock, end_clock, train_organ_id, publish_time)" +
            "values(#{trainClassTimeId}, #{trainId}, #{trainClassNo}, #{startTime}, #{endTime}, #{requirePopulation}, #{fee}, " +
            "#{state}, #{contact}, #{startClock}, #{endClock}, #{trainOrganId}, current_timestamp())")
    void insertTrainClassTime(
            Integer trainId,
            Integer trainClassNo,
            String startTime,
            String endTime,
            Integer requirePopulation,
            double fee,
            String contact,
            String startClock,
            String endClock,
            String state,
            Integer trainOrganId,
            Integer trainClassTimeId
    );

    @Insert("insert into good(id) VALUES(NULL)")
    void insertGood();

    @Select("select last_insert_id()")
    Integer selectLastInsertId();

    @Select("select * from train_class_time where id = #{trainClassTimeId}")
    TrainClassTime selectTrainClassTimeBYId(Integer trainClassTimeId);

    @Update("update train_class_time set train_id = #{trainId}, train_class_No = #{trainClassNo}, " +
            "start_time = #{startTime}, end_time = #{endTime}, number_limit = #{requirePopulation}, price = #{fee}, " +
            "contact = #{contact}, start_clock = #{startClock}, end_clock = #{endClock} " +
            "where id = #{trainClassTimeId}")
    void updateTrainClassTime(
            Integer trainId,
            Integer trainClassNo,
            String startTime,
            String endTime,
            Integer requirePopulation,
            double fee,
            String contact,
            String startClock,
            String endClock,
            Integer trainClassTimeId
    );

    @Select("select id from train_organ where username = #{username}")
    Integer selectTrainOrganId(String username);

    @Select("select name, contact, startTime, endTime from train_class_time_basic_information_view " +
            "where trainClassTimeId = #{trainClassTimeId}")
    Map<String, Object> selectTrainClassTimeBasicInformation(@Param("trainClassTimeId") Integer trainClassTimeId);

    @Select("select enrollCount, clickCount, shareCount from train_class_time_index_view " +
            "where trainClassTimeId = #{trainClassTimeId}")
    Map<String, Object> selectTrainClassTimeIndex(@Param("trainClassTimeId") Integer trainClassTimeId);

    @Select("SELECT last_login_time FROM user where username=#{username}")
    Map<String, Object> SelectLastTime(String username);

    @Select("SELECT * from train_class_new_enroll where date<=#{time} and organUsername=#{username}")
    List<Map<String, Object>> SelectNewTrainClass(Timestamp time, String username);

    @Select("select professionId from profession_id_by_train_class_time_id_view where trainClassTimeId = #{id}")
    Integer selectProfessionIdByTrainClassTimeId(Integer id);

    @Select("select name, requirePopulation, enrollPopulation, state, publishTime " +
            "from recent_editor_train_class_time where username = #{username}")
    List<Map<String, Object>> selectRecentEditorTrainClassTime(String username);

    @Select("select publish_time from train_class_time where id = #{trainClassTimeId}")
    String selectPublicTimeByTrainClassTimeId(Integer trainClassTimeId);

    @Select("select publishTime, passTime, publisherName, trainClassName, credentialId, id, state, picture, clickCount, trainId " +
            "from train_class_time_general_with_picture " +
            "where (trainClassName like #{keyWord} or publisherName like #{keyWord}) and credentialId=#{credentialId} and if(#{state}=null, state=#{state}, true)")
    List<Map<String, Object>> selectTrainClassGeneralByKeyWordAndCredential(String keyWord, Integer credentialId, String state);

    @Select("select * from train_class_time_general_with_picture where id=#{trainClassTimeId}")
    Map<String, Object> selectTrainClassTimeSummaryWithPicture(Integer trainClassTimeId);

    @Update("update train_class_time set state=#{state}, pass_time=IF(#{state}='已发布',CURRENT_TIMESTAMP(),NULL) where id=#{trainClassTimeId}")
    Integer updateStateOfTrainClassTime(Integer trainClassTimeId, String state);

    @Select("select * from train_class_general where id=#{id}")
    Map<String, Object> selectTrainClassTimeById(@Param("id") Integer trainClassTimeId);

}
